package com.kevin.service;

import java.sql.Connection;
import java.sql.DatabaseMetaData;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import java.util.Properties;

import com.kevin.model.db.DB;
import com.kevin.model.db.DBSetInfo;
import com.kevin.model.db.DBTableColumnModel;
import com.kevin.model.db.DBTableModel;
import com.kevin.service.impl.MysqlTabelService;
import com.kevin.service.impl.OracleTabelService;

import lombok.Data;
import lombok.extern.log4j.Log4j;

/**
 * table转model服务
 * 
 * @ClassName Tabel2ModelService
 * @author 朱鹏程
 * @date 2016年8月23日 上午9:23:00
 *
 */
@Data
@Log4j
public abstract class DBTabelService {

    public DBTabelService() {
        init();
    }

    protected void init() {
    }

    /**
     * 加载DB服务类
     * 
     * @Title loadDBTableService
     * @author 吕凯
     * @date 2016年8月25日 下午5:33:27 void
     */
    public static DBTabelService getInstance() {
        DBTabelService dbTableService = null;
        String dbType = DB.getDBType();
        if (DB.ORACLE.equals(dbType)) {
            dbTableService = new OracleTabelService();
        } else if (DB.MYSQL.equals(dbType)) {
            dbTableService = new MysqlTabelService();
        } else {// 其他默认使用Oracle
            log.error("没有匹配的数据库操作服务");
            dbTableService = new OracleTabelService();
        }
        return dbTableService;
    }

    /**
     * 数据库中字段类型转java类型
     * 
     * @Title oracleType2JavaType
     * @author 吕凯
     * @date 2016年8月25日 上午10:52:12
     * @param sqlType
     * @param scale
     * @param size
     * @return String
     */
    public static String dbType2JavaType(String sqlType, int scale, int size) {
        if (sqlType != null) {
            sqlType = sqlType.toLowerCase();
        }
        if (sqlType.equals("integer") || sqlType.equals("int") || sqlType.equals("tinyint")) {
            return "Integer";
        } else if (sqlType.equals("long") || sqlType.equals("bigint")) {
            return "Long";
        } else if (sqlType.equals("float") || sqlType.equals("float precision") || sqlType.equals("double")
                || sqlType.equals("double precision")) {
            return "Double";
        } else if (sqlType.equals("number") || sqlType.equals("decimal") || sqlType.equals("numeric") || sqlType.equals("real")) {
            return scale == 0 ? (size < 10 ? "Integer" : "Long") : "Double";
        } else if (sqlType.equals("varchar") || sqlType.equals("varchar2") || sqlType.equals("char") || sqlType.equals("nvarchar2")
                || sqlType.equals("nchar") || sqlType.equals("text") || sqlType.equals("longtext") || sqlType.equals("json")
                || sqlType.equals("clob")) {
            return "String";
        } else if (sqlType.equals("datetime") || sqlType.equals("date") || sqlType.equals("timestamp")) {
            return "Date";
        }
        return null;
    }

    /**
     * 根据表名返回列信息（通用，部分不兼容的数据库可重写）
     * 
     * @Title getTableColumn
     * @author 吕凯
     * @date 2016年8月25日 上午10:12:06
     * @param tableName
     * @return List<DBTableColumnModel>
     */
    public List<DBTableColumnModel> getTableColumn(String tableName) {
        List<DBTableColumnModel> tableList = new ArrayList<>();
        ResultSet colRet = null;
        Properties props = new Properties();
        props.put("remarksReporting", "true");// 注意这里
        props.put("user", DBSetInfo.getDbUserName());
        props.put("password", DBSetInfo.getDbPassword());
        try (Connection conn = DriverManager.getConnection(DBSetInfo.getDbUrl(), props)) {
            DatabaseMetaData dbmd = conn.getMetaData();
            colRet = dbmd.getColumns(null, DBSetInfo.getDbUserName().toUpperCase(), tableName, "%");
            while (colRet.next()) {
                String columnName = colRet.getString("COLUMN_NAME");
                String remarks = colRet.getString("REMARKS");
                String columnType = colRet.getString("TYPE_NAME");
                int datasize = colRet.getInt("COLUMN_SIZE");
                int digits = colRet.getInt("DECIMAL_DIGITS");
                int nullable = colRet.getInt("NULLABLE"); // 返回1就表示可以是Null,而0就表示Not Null。
                DBTableColumnModel tableColumn = new DBTableColumnModel();
                tableColumn.setTableName(tableName);
                tableColumn.setColumnName(columnName);
                tableColumn.setColumnType(columnType);
                tableColumn.setDatasize(datasize);
                tableColumn.setDigits(digits);
                tableColumn.setRemarks(remarks);
                tableColumn.setNullable(nullable == 1 ? true : false);
                tableList.add(tableColumn);
            }

        } catch (Exception e) {
            log.error("查询用户" + DBSetInfo.getDbUserName() + "下的表" + tableName + "出错！", e);
        } finally {
            if (colRet != null) {
                try {
                    colRet.close();
                } catch (SQLException e) {
                    log.error("关闭出错！", e);
                }
            }

        }
        return tableList;
    }

    /**
     * 测试连接是否正常（通用，部分不兼容的数据库可重写）
     * 
     * @Title testCon
     * @author 吕凯
     * @date 2016年8月29日 上午9:15:24
     * @param url
     * @param userName
     * @param password
     * @return boolean
     */
    public static boolean testCon(String url, String userName, String password) {
        boolean flag = false;
        String strsql = " SELECT 1 FROM dual ";
        PreparedStatement pstmt = null;
        try (Connection conn = DriverManager.getConnection(url, userName, password)) {
            pstmt = conn.prepareStatement(strsql);
            pstmt.executeQuery();
            flag = true;
        } catch (Exception e) {
            log.error("数据库连接出错" + url + " " + userName + " " + password + "！", e);
        } finally {
            if (pstmt != null) {
                try {
                    pstmt.close();
                } catch (SQLException e) {
                    log.error("关闭出错！", e);
                }
            }

        }
        return flag;
    }

    /**
     * 获取table信息（子类实现）
     * 
     * @Title getTables
     * @author 吕凯
     * @date 2017年3月24日 下午5:29:26
     * @param name
     *            表名关键字
     * @return List<DBTableModel>
     */
    public abstract List<DBTableModel> getTables(String name);

    /**
     * 获取所有表（已废弃）
     * 
     * @Title getAllTables
     * @author 吕凯
     * @date 2017年3月24日 下午5:32:00
     * @param name
     * @return List<DBTableModel>
     */
    @Deprecated
    public List<DBTableModel> getAllTables(String name) {
        List<DBTableModel> tableList = new ArrayList<>();
        ResultSet rsmd = null;
        try (Connection conn = DriverManager.getConnection(DBSetInfo.getDbUrl(), DBSetInfo.getDbUserName(), DBSetInfo.getDbPassword())) {
            DatabaseMetaData dbMetaData = conn.getMetaData();
            // 可为:"TABLE", "VIEW", "SYSTEM TABLE",
            // "GLOBAL TEMPORARY", "LOCAL TEMPORARY", "ALIAS", "SYNONYM"
            String[] types = { "TABLE" };
            rsmd = dbMetaData.getTables(null, null, null, types/*只要表就好了*/);
            /*记录集的结构如下:   
            TABLE_CAT       String   =>   table   catalog   (may   be   null)     
            TABLE_SCHEM   String   =>   table   schema   (may   be   null)     
            TABLE_NAME     String   =>   table   name     
            TABLE_TYPE     String   =>   table   type.     
            REMARKS           String   =>   explanatory   comment   on   the   table     
            TYPE_CAT         String   =>   the   types   catalog   (may   be   null)     
            TYPE_SCHEM     String   =>   the   types   schema   (may   be   null)     
            TYPE_NAME       String   =>   type   name   (may   be   null)     
            SELF_REFERENCING_COL_NAME   String   =>   name   of   the   designated   "identifier"   column   of   a   typed   table   (may   be   null)     
            REF_GENERATION   String   =>   specifies   how   values   in   SELF_REFERENCING_COL_NAME   are   created.   Values   are   "SYSTEM",   "USER",   "DERIVED".   (may   be   null)     
             */
            while (rsmd.next()) {
                String tableName = rsmd.getString("TABLE_NAME");
                Long dataSize = rsmd.getLong("TABLE_ROWS");
                String comments = rsmd.getString("REMARKS");
                DBTableModel model = new DBTableModel();
                model.setTableName(tableName);
                model.setDataSize(dataSize);
                model.setTableComments(comments);
                tableList.add(model);

            }
        } catch (Exception e) {
            log.error("查询用户" + DBSetInfo.getDbUserName() + "下的表出错！", e);
        } finally {
            if (rsmd != null) {
                try {
                    rsmd.close();
                } catch (SQLException e) {
                    log.error("关闭出错！", e);
                }
            }

        }
        System.out.println(tableList);
        return tableList;

    }

}
